import numpy as np
import pandas as pd
import pymysql

class MySqlUtils(object):
    def __init__(self):
        self.conn = pymysql.connect(
            host='localhost',
            user='root',
            password='root',
            database='scenic',
            port=3306,
            charset='utf8',
            cursorclass=pymysql.cursors.DictCursor
        )
    
    def get_data(self):
        cursor = self.conn.cursor()
        sql = """
            SELECT 
            t.tourist_agency_name, rel.id_no, 
            LEFT(rel.id_no, 2) as province_code, 
            DAYOFWEEK(gate.create_time) as weekend, 
            CAST(SUBSTRING(rel.id_no, 7, 4)as UNSIGNED) as birth_year 
            from scenic.ticket_order_user_rel rel 
            JOIN scenic.ticket_order t on t.id =rel.order_id
            JOIN scenic.order_user_gate_rel gate on gate.ticket_rel_id = rel.id
            WHERE t.tourist_agency_name != '' AND t.pay_time != ''
        """
        cursor.execute(sql)
        data = cursor.fetchall()
        df = pd.DataFrame(data)

        # 新增非周末标记
        df['non_weekend'] = df['weekend'].apply(lambda x: 1 if x not in [1, 7] else 0)
        
        # 新增身份证有效性标记
        df['valid_id'] = df['id_no'].apply(lambda x: 1 if str(x).strip() != '' else 0)

        # 新增老年人标记（修正拼写：eldery_ratio → elderly_ratio）
        df['elderly_ratio'] = df.apply(
            lambda x: 1 if (x['valid_id'] and 2025 - x['birth_year'] >= 60) else 0 if x['valid_id'] else np.nan, 
            axis=1
        )
        
        # 新增外省游客标记
        df['out_province'] = df.apply(
            lambda x: 1 if (x['valid_id'] and x['province_code'] != '44') else 0 if x['valid_id'] else np.nan, 
            axis=1
        )
        
        # 分组聚合（修正字段名：valid_id_visitors 是有效游客数的聚合结果）
        result = df.groupby('tourist_agency_name').agg(
            total_visitors=('id_no', 'count'),  # 总游客数
            non_weekend_visitors=('non_weekend', 'sum'),  # 非周末人数
            valid_visitors=('valid_id', 'sum'),  # 有效游客数（修正：从non_weekend改为valid_id）
            elderly_ratio_visitors=('elderly_ratio', 'sum'),  # 老年人人数（修正拼写）
            out_province_visitors=('out_province', 'sum'),  # 外省游客人数
        ).reset_index()

        # 计算占比（修正字段名和逻辑）
        result['elderly_ratio'] = result['elderly_ratio_visitors'] / result['valid_visitors'].replace(0, np.nan)
        result['non_weekend_ratio'] = result['non_weekend_visitors'] / result['total_visitors'].replace(0, np.nan)
        result['out_province_ratio'] = result['out_province_visitors'] / result['valid_visitors'].replace(0, np.nan)
        result = result[result['valid_visitors'] > 0]
# 过滤占比列的NaN值
        result = result.dropna(subset=['elderly_ratio', 'non_weekend_ratio', 'out_province_ratio'])

        print(result.head())
        result.to_csv('scenic_data.csv', index=False)
        
        # 关闭数据库连接
        cursor.close()
        self.conn.close()

if __name__ == '__main__':
    mysql_utils = MySqlUtils()
    mysql_utils.get_data()
